﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace AppTrans
{
    class PayablesObjectNew
    {
        public const string sql = @"
SELECT DictionaryPartners.Name as Партнер
	,report.CountWorks as Кол__во_Работ
	,report.SumDolg as Сумма_Долга
	,report.SumOplat as Сумма_Оплат
	,report.SumDolg - report.SumOplat as Долг_Поставщику
	,DictionaryCurrency.Name as Валюта
	,DictionaryProject.Name as Проект
FROM
(
	SELECT w1.Partner as Partner
		,COALESCE(w1.CountWorks,0) as  CountWorks
		,COALESCE(w1.SumDolg,0) as SumDolg
		,COALESCE(t1.SumOplat,0) as SumOplat
		,w1.Currency as Currency
		,w1.Project as Project
	FROM
		(SELECT [Work].[Supplier] as Partner
			   ,Sum(COALESCE([Work].[CountWork],0)) as CountWorks
			   ,Sum(COALESCE([Work].[PriceOfPurchasing],0) * COALESCE([Work].[CountWork],0)) as SumDolg
			   ,[Work].[CurrencyPurchasing] as Currency
			   ,[Work].[Project] as Project
		FROM   [Work]
		Group by [Work].[Supplier], [Work].[Project], [Work].[CurrencyPurchasing]) as w1
	LEFT JOIN
		(SELECT [Transactions].[Partner] as Partner
			   ,sum(COALESCE([Summa],0)) as SumOplat
			   ,[Transactions].[Currency] as Currency
			   ,[Transactions].[Project] as Project
		FROM   [Transactions]
		WHERE  [Transactions].[Type] = 2 and [Transactions].[Category] in (select convert(int,Value) from Settings where Code Like 'code_cat_racx_suppl%')
		Group by [Transactions].[Partner], [Transactions].[Project], [Transactions].[Currency]) as t1
	ON w1.Partner = t1.Partner and w1.Project = t1.Project and w1.Currency = t1.Currency
UNION ALL
	SELECT t1.Partner as Partner
		,COALESCE(w1.CountWorks,0) as  CountWorks
		,COALESCE(w1.SumDolg,0) as SumDolg
		,COALESCE(t1.SumOplat,0) as SumOplat
		,t1.Currency as Currency
		,t1.Project as Project
	FROM
			(SELECT [Transactions].[Partner] as Partner
			   ,sum(COALESCE([Summa],0)) as SumOplat
			   ,[Transactions].[Currency] as Currency
			   ,[Transactions].[Project] as Project
		FROM   [Transactions]
		WHERE  [Transactions].[Type] = 2 and [Transactions].[Category] in (select convert(int,Value) from Settings where Code Like 'code_cat_racx_suppl%')
		Group by [Transactions].[Partner], [Transactions].[Project], [Transactions].[Currency]) as t1
	LEFT JOIN
		(SELECT [Work].[Supplier] as Partner
			   ,Sum(COALESCE([Work].[CountWork],0)) as CountWorks
			   ,Sum(COALESCE([Work].[PriceOfPurchasing],0) * COALESCE([Work].[CountWork],0)) as SumDolg
			   ,[Work].[CurrencyPurchasing] as Currency
			   ,[Work].[Project] as Project
		FROM   [Work]
		Group by [Work].[Supplier], [Work].[Project], [Work].[CurrencyPurchasing]) as w1
	ON t1.Partner = w1.Partner and t1.Project = w1.Project and t1.Currency = w1.Currency
	WHERE w1.Partner is null and w1.CountWorks is null and w1.SumDolg is null and w1.Currency is null and w1.Project is null
) as report

INNER JOIN DictionaryPartners on DictionaryPartners.ID = report.Partner
INNER JOIN DictionaryCurrency on DictionaryCurrency.ID = report.Currency
INNER JOIN DictionaryProject on DictionaryProject.ID = report.Project

ORDER BY Партнер, Кол__во_Работ, Долг_Поставщику, Валюта, Проект
";
        public string Партнер { get; set; }
        public int Кол__во_Работ { get; set; }
        public decimal? Сумма_Долга { get; set; }
        public decimal? Сумма_Оплат { get; set; }
        public decimal? Долг_Поставщику { get; set; }
        public string Валюта { get; set; }
        public string Проект { get; set; }
    }
}
